<!--- 
LICENSE INFORMATION:

Copyright 2007, Hem Talreja
 
Licensed under the Apache License, Version 2.0 (the "License"); you may not 
use this file except in compliance with the License. 

You may obtain a copy of the License at 

	http://www.apache.org/licenses/LICENSE-2.0 
	
Unless required by applicable law or agreed to in writing, software distributed
under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR 
CONDITIONS OF ANY KIND, either express or implied. See the License for the 
specific language governing permissions and limitations under the License.

VERSION INFORMATION:	(0.001)

 --->
<cfcomponent name="dbWriter" extends="dbHelper" output="false">
	
	<cffunction name="init" returntype="dbWriter" access="public" output="false">
		<cfargument name="dbProperties" type="any" required="true" />
		<cfargument name="dbDataTypes" type="any" required="true" />
		
		<!--- call setter functions --->
		<cfset setDBProperties(Arguments.dbProperties) />
		<cfset setDBDataTypes(Arguments.dbDataTypes) />
		
		<cfreturn this />
	</cffunction>

	
	<cffunction name="dbChange" access="public" returntype="struct" output="false" >
		<cfargument name="FieldValues" type="struct" required="yes" hint="Colmun Field Names and Values" />
		<cfargument name="FieldStruct" type="struct" required="true" hint="Structure of the table that needs to be parsed" />
		<cfargument name="TableName" type="string" required="yes" hint="Name of the Table to update the columns" />
		<cfargument name="PrimaryKey" type="string" required="No" hint="Primary Key of the table" />
		<cfargument name="CounterKey" type="string" required="No" default="" hint="Auto Increment ID for this Table" />
		<cfargument name="ActionPermited" type="string"  required="No" default="Any" hint="The Action you want the system to perform [Any,Insert,Update,None]">
		
		<!--- define local scope to work with --->
		<cfset var Local = StructNew() />
		<cfset var I = "" />
			
		<cfset Local.Proceed = true />
		<cfset Local.strMessage = "" />
		<cfset Local.strActionToPerform = "INSERT" />	<!--- What action is requested to be performed --->
		<cfset Local.strActionPerformed = "" />			<!--- What action was performed --->
		<cfset Local.blnSuccess = false />				<!--- Was the action to be performed completed succesfully --->
		<cfset Local.NewDBChangeKey = "" />
		
		<cfset Local.blnInsertAllowed = true />			<!--- is insert action allowed --->
		<cfset Local.blnUpdateAllowed = true />			<!--- is update action allowed --->
		<cfset Local.blnActionPerformed = false />		<!--- was and action was performed --->
		
		<cfset Local.blnPKPresent = false />			<!--- Primary key is present --->
		<cfset Local.blnCKPresent = false />			<!--- Counter Key is present --->
		<cfset Local.blnCKisPK = false    />			<!--- The counter key is primary --->				
		<cfset Local.intRecordsPresent = 0 />
		
		<!--- ok the where clause goes here. --->
		<cfset Local.lstWhereColumns = "" />				<!--- list of key's will contain counter key or primary key, may be all key's --->
		<cfset Local.strQuery = "" />					<!---  Query String that will be executed to update or insert data --->
		<cfset Local.strColNames = "" />
		<cfset Local.strColValues = "" />
		
		<cfset Local.strQueryUpdated = "" />			<!--- This is the portion of the where clause that is used to find the records just inserted --->
		
			
		<!--- get the list of columns that were passed to us that are present in the table --->
		<cfset Local.lstTableColumns = getUniqueList(StructKeyList(Arguments.FieldValues), StructKeyList(Arguments.FieldStruct)) />
		
		<cfscript>
			/* action permited */
			if( Arguments.ActionPermited is "INSERT" ){
				Local.blnUpdateAllowed = false;	// is update action allowed
			}else if( Arguments.ActionPermited is "UPDATE"){
				Local.blnInsertAllowed = false;	// is insert action allowed
			}else if( Arguments.ActionPermited is  "NONE" ){
				Local.blnInsertAllowed = false;	// is insert action allowed
				Local.blnUpdateAllowed = false;	// is update action allowed
			}
			
			if( Local.lstTableColumns IS "" ){
				Local.Proceed = false;
				Local.strMessage = "database columns were not provided";
			}else if( ListLen(Local.lstTableColumns) LT 2 ){
				Local.Proceed = false;
				Local.strMessage = "At least two columns are required to be provided!";
			}
		</cfscript>
		
		<!--- ok we have all the desired data. let's start the processing --->
		<cfif Local.Proceed>
			
			<!--- more than one key's may be primary, see if all key's are present --->
			<cfif isDefined("Arguments.PrimaryKey") AND Arguments.PrimaryKey IS NOT "">
				
				<cfset Local.blnPKPresent = true />	<!--- set pk as present, we will unset it if the field is not found --->
				
				<cfloop list="#Arguments.PrimaryKey#" index="I">
					<cfif NOT isDefined("Arguments.FieldValues.#I#") OR Arguments.FieldValues[I] IS "">
						<cfset Local.blnPKPresent = false />
					</cfif>
				</cfloop>
					
			</cfif>	<!--- check for pk existance in data --->
			
			<!--- ok let's see if the counterkey is present --->
			<cfif isDefined("Arguments.CounterKey") AND Arguments.CounterKey IS NOT "">
				
				<cfset Local.blnCKPresent = true />
				
				<!--- ok the counter key is present and the primary key is present too --->
				<cfif Local.blnPKPresent AND Arguments.CounterKey IS Arguments.PrimaryKey>
					<cfset Local.blnCKisPK = true />
				<cfelse>
					<cfloop list="#Arguments.CounterKey#" index="I">
						<cfif NOT isDefined("Arguments.FieldValues.#I#") 
									OR Arguments.FieldValues[I] IS "">
							<cfset Local.blnCKPresent = false />
						</cfif>
					</cfloop>
				</cfif>
			
			</cfif>	<!--- is the counter key present --->
			
			
			<cfif Local.blnCKPresent AND Local.blnPKPresent AND NOT Local.blnCKisPK>
				
				<cfset Local.blnUseSeperator = false />
				<cfquery name="Local.getPKRecords" datasource="#getDBProperties().getDataSource()#">
					SELECT 	#Arguments.CounterKey# AS CounterKeyVal
					FROM 	#Arguments.TableName#
					WHERE 	<!--- loop through each column and get the column's data --->
							<cfloop list="#Arguments.PrimaryKey#" index="I">
									<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
									<cfif Local.blnUseSeperator>AND</cfif>
									<cfset Local.blnUseSeperator = true />
								#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
							</cfloop>
				</cfquery>
				
				<cfset Local.blnUseSeperator = false />
				<cfquery name="Local.getCKRecords" datasource="#getDBProperties().getDataSource()#">
					SELECT 	#Arguments.CounterKey# AS CounterKeyVal
					FROM 	#Arguments.TableName#
					WHERE 	<!--- loop through each column and get the column's data --->
							<cfloop list="#Arguments.CounterKey#" index="I">
									<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
									<cfif Local.blnUseSeperator>AND</cfif>
									<cfset Local.blnUseSeperator = true />
								#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
							</cfloop>
				</cfquery>
				
				<cfif Local.getPKRecords.RecordCount EQ 0 AND Local.getCKRecords.RecordCount EQ 1>
				
							<cfset Local.lstWhereColumns = Arguments.CounterKey />
							<cfset Local.strActionToPerform = "UPDATE" />
				
				<cfelseif Local.getPKRecords.RecordCount EQ 1 AND Local.getCKRecords.RecordCount EQ 1>
				
					<cfif Local.getPKRecords.CounterKeyVal EQ Local.getPKRecords.CounterKeyVal>
							<cfset Local.lstWhereColumns = Arguments.CounterKey />
							<cfset Local.strActionToPerform = "UPDATE" />
					<cfelse>
							<cfset Local.Proceed = false />
							<cfset Local.strMessage = "The updated Primary Key value already exists in the database #Arguments.PrimaryKey#" />
					</cfif>
				
				</cfif>
				
			<cfelseif Local.blnPKPresent OR Local.blnCKPresent>
				
				<cfif Local.blnPKPresent>
					<cfset Local.lstWhereColumns = Arguments.PrimaryKey />
				<cfelse>
					<cfset Local.lstWhereColumns = Arguments.CounterKey />
				</cfif>
				
				<cfquery name="Local.selTableRecords" datasource="#getDBProperties().getDataSource()#">
					SELECT 	Count(*) AS NumberOfRecords
					FROM 	#Arguments.TableName#
					WHERE 	<cfset Local.blnUseSeperator = false />
							<cfloop list="#Local.lstWhereColumns#" index="I">
									<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
									<cfif Local.blnUseSeperator>AND</cfif>
									<cfset Local.blnUseSeperator = true />
								#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
							</cfloop>
				</cfquery>
				
				<cfif Val(Local.selTableRecords.NumberOfRecords)>
					<cfset Local.strActionToPerform = "UPDATE" />
				</cfif>
				
			</cfif>
			
		</cfif>	<!--- Local.Proceed --->
		
			
		<cfif Local.Proceed>
			<!--- ok now ckeck for action to be performed --->
			<cfif Local.strActionToPerform IS  "UPDATE" AND  Local.blnUpdateAllowed>
				
				<cfset Local.blnActionPerformed = true />
				
				<cfquery datasource="#getDBProperties().getDataSource()#" result="Local.dbChangeQuery">
					UPDATE #Arguments.TableName# SET 		
					
					<!--- loop through each column and get the column's data --->
					<cfset Local.blnUseSeperator = false />
					<cfloop list="#Local.lstTableColumns#" index="I"><cfif NOT ListFindNoCase(Arguments.CounterKey,I)>
						<cfif Local.blnUseSeperator>,</cfif>
						<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
						<cfset Local.blnUseSeperator = true />
						#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
					</cfif></cfloop>	<!--- Local.lstTableColumns --->
					
					<cfif Local.lstWhereColumns IS NOT "">
						WHERE 	<cfset Local.blnUseSeperator = false />
								<cfloop list="#Local.lstWhereColumns#" index="I">
									<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
									<cfif Local.blnUseSeperator>AND</cfif>
									<cfset Local.blnUseSeperator = true />
									#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
								</cfloop>	
					</cfif>
				</cfquery>

				
			<cfelseif Local.strActionToPerform IS "INSERT" AND Local.blnInsertAllowed>
				
				<cfset Local.blnActionPerformed = true />
				<cfset Local.blnUseSeperator = false />
				
				<cfquery datasource="#getDBProperties().getDataSource()#" result="Local.dbChangeQuery">
					INSERT INTO #Arguments.TableName# ( #Local.lstTableColumns# ) VALUES
					( 
						<cfloop list="#Local.lstTableColumns#" index="I">
							<cfif Local.blnUseSeperator>,</cfif>
							<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
							<cfset Local.blnUseSeperator = true />
							<cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
						</cfloop>	
					)
				</cfquery>
				
				
			<cfelse>
				
				<cfset Local.Proceed = false />
				<cfset Local.strMessage = "Insert or update was not permitted" />
			
			</cfif>
		</cfif>
		
		<!--- run the insert or update query --->
		<cfif Local.Proceed>
			
			<cfset Local.strActionPerformed = Local.strActionToPerform />
			<cfset Local.blnSuccess = true />	<!--- query was updated successfully --->
			
			<cfif IsDefined("Local.dbChangeQuery.IdentityCol")>
				<!--- Identity Col is the identity value associated to this record in MS SQL --->
				<cfset Local.NewDBChangeKey = Local.dbChangeQuery.IdentityCol />
			
			<cfelseif IsDefined("Local.dbChangeQuery.Generated_Key")>
				<!--- The generated key is the identity column associated to this record in MySQL --->
				<cfset Local.NewDBChangeKey = Local.dbChangeQuery.Generated_Key />
			
			<cfelseif Arguments.CounterKey IS NOT "" AND Local.lstWhereColumns IS NOT "">
				
				<cfquery name="Local.getCKRecords" datasource="#getDBProperties().getDataSource()#">
						SELECT 	#Arguments.CounterKey# AS CounterKeyVal
						
						FROM 	#Arguments.TableName#
						
						WHERE	<cfset Local.blnUseSeperator = false />
								<cfloop list="#Local.lstWhereColumns#" index="I">
									<cfset Local.stcColInfo = getColumnQueryParams(I, Arguments.FieldValues, Arguments.FieldStruct) />
									<cfif Local.blnUseSeperator>AND</cfif>
									<cfset Local.blnUseSeperator = true />
									#I# = <cfqueryparam cfsqltype="#Local.stcColInfo.cfsqltype#" value="#Local.stcColInfo.value#"  />
								</cfloop>
				</cfquery>
				
				<cfif Local.getCKRecords.recordCount>
					<cfset Local.NewDBChangeKey = Local.getCKRecords.CounterKeyVal />
				</cfif>
			
			</cfif>
			
				
		</cfif>	<!--- Local.Proceed: run the insert or update query --->
		
		
		<!--- Process the return of the data --->
		<cfset Local.stcReturn = StructNew() />
		<cfset Local.stcReturn.Success = Local.blnSuccess />
		<cfset Local.stcReturn.Message = Local.strMessage />
		<cfset Local.stcReturn.ActionPerformed = Local.strActionPerformed />
		<cfset Local.stcReturn.ActionToBePerformed = Local.strActionToPerform />
		<cfset Local.stcReturn.dbChangeKey = Local.NewDBChangeKey />
		
		<cfif isDefined("Local.dbChangeQuery")>
			<cfset Local.stcReturn.QueryString = Local.dbChangeQuery.SQL />
			<cfset Local.stcReturn.dbChangeQuery = Local.dbChangeQuery />
			<cfset Local.stcReturn.RecordCount = Local.dbChangeQuery.RecordCount />
		</cfif>
		
		<cfreturn Local.stcReturn />
	</cffunction>	<!--- dbChange --->

	
	<!--- Possibly need to move this function to Application.logic.system.utilities --->
	<cffunction name="getUniqueList" access="package" returntype="string" output="false">
		<cfargument name="List1" type="string" required="true" />
		<cfargument name="List2" type="string" required="true" />
		
		<cfset var strReturn = "" />
		<cfset var i = "" />
		
		<cfloop list="#Arguments.List1#" index="i">
			<cfif ListFindNoCase(Arguments.List2,i)>
				<cfset strReturn = ListAppend(strReturn,i) />
			</cfif>
		</cfloop>
		
		<cfreturn strReturn />
	</cffunction>
	
	
	
	<cffunction name="getColumnQueryParams" access="package" returntype="struct" output="false">
		<cfargument name="Column" type="string" required="true" />
		<cfargument name="FieldValues" type="struct" required="yes" hint="Colmun Field Names and Values" />
		<cfargument name="FieldStruct" type="struct" required="true" hint="Structure of the table that needs to be parsed" />
		
		<!---	<cfqueryparam cfsqltype="" value="" maxlength="" scale="" null="" /> --->	
		
		<cfset var Local = StructNew() />
		<cfset var stcReturn = StructNew() />
		
		<cfset stcReturn.value = getColumnValue(Column, FieldValues) />
		<cfset stcReturn.dbDataType = getColumnDataType(Column, FieldStruct) />
		<cfset stcReturn.cfSqlType = getDBDataTypes().getCFQueryDataType( stcReturn.dbDataType )  />
		<cfset stcReturn.null = getColumnIsNullable(Column, FieldStruct) />
		
		<cfset stcReturn.value = getColumnData(stcReturn.value,stcReturn.dbDataType, stcReturn.cfSqlType) />
		
		<cfreturn stcReturn />
	</cffunction>

	
	<cffunction name="getColumnValue" access="package" returntype="string" output="false">
		<cfargument name="ColumnName" type="string" required="true" />
		<cfargument name="FieldValues" type="struct" required="yes" hint="Colmun Field Names and Values" />
		
		<cfreturn Arguments.FieldValues[Arguments.ColumnName] />
	</cffunction>
	<cffunction name="getColumnDataType" access="package" returntype="string" output="false">
		<cfargument name="ColumnName" type="string" required="true" />
		<cfargument name="FieldStruct" type="struct" required="true" hint="Structure of the table that needs to be parsed" />

		<cfreturn Arguments.FieldStruct[Arguments.ColumnName]["DATA_TYPE"] />
	</cffunction>
	
	<cffunction name="getColumnIsNullable" access="package" returntype="string" output="false">
		<cfargument name="ColumnName" type="string" required="true" />
		<cfargument name="FieldStruct" type="struct" required="true" hint="Structure of the table that needs to be parsed" />
	
		<cfreturn Arguments.FieldStruct[Arguments.ColumnName]["IS_NULLABLE"] />
	</cffunction>
	
	<!--- Function will return data that will be saved in the database. --->
	<cffunction name="getColumnData" access="package" returntype="string" output="false">
		<cfargument name="ColumnValue" type="string" required="true" />
		<cfargument name="dbDataType" type="string" required="true" />
		<cfargument name="cfDataType" type="string" required="true" />
		
		<cfset var strReturn = Arguments.ColumnValue />
		
		<cfswitch expression="#dbDataType#">
		
			<cfcase value="encrypted">
				<cfreturn getDBProperties().EncryptData(Arguments.ColumnValue) />
			</cfcase>		
		
		</cfswitch>
		
		<cfreturn strReturn />
	</cffunction>
	
</cfcomponent>